﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace ConcordWinManager
{
    /// <summary>
    /// Контрагент
    /// </summary>
    public class Contragent
    {


        #region [из таблицы Contragent]

        /// <summary>
        /// Идентификатор контрагента
        /// </summary>
        public int id_contragent;

        /// <summary>
        /// Тип контрагента
        /// </summary>
        public string typeContragent; //(ФЛ, ЮЛ, ИП)

        /// <summary>
        /// Идентификатор физ.лица
        /// </summary>
        public int id_fiz_lico;

        /// <summary>
        /// Идентификатор юр.лица
        /// </summary>
        public int id_ur_lico;

        /// <summary>
        /// Идентификатор ИП
        /// </summary>
        public int id_ind_predp;

        /// <summary>
        /// Домашний телефон
        /// </summary>
        public string telHome;

        /// <summary>
        /// Рабочий телефон
        /// </summary>
        public string telWork;

        /// <summary>
        /// Мобильный телефон
        /// </summary>
        public string telMobile;

        /// <summary>
        /// Адрес электронной почты
        /// </summary>
        public string email;

        /// <summary>
        /// Примечание
        /// </summary>
        public string note;

        /// <summary>
        /// Идентификатор адреса
        /// </summary>
        public int id_address;

        /// <summary>
        /// Адрес
        /// </summary>
        public Address address;

        #endregion


        #region [из таблицы Fiz_lico]

        /// <summary>
        /// Фамилия
        /// </summary>
        public string Family=string.Empty;

        /// <summary>
        /// Имя
        /// </summary>
        public string Name = string.Empty;

        /// <summary>
        /// Отчество
        /// </summary>
        public string FName = string.Empty;

        /// <summary>
        /// Пол
        /// </summary>
        public bool Male;

        /// <summary>
        /// Дата рождения
        /// </summary>
        public DateTime Birth;

        /// <summary>
        /// Серия паспорта
        /// </summary>
        public string PassportSeries;

        /// <summary>
        /// Номер паспорта
        /// </summary>
        public string PassportNumber;

        /// <summary>
        /// Когда и кем выдан
        /// </summary>
        public string PassportVidan;

        #endregion

        #region [из таблицы Ur_lico]

        /// <summary>
        /// Название
        /// </summary>
        public string UName;

        /// <summary>
        /// ИНН
        /// </summary>
        public string INN;

        /// <summary>
        /// КПП
        /// </summary>
        public string KPP;

        /// <summary>
        /// ОГРН
        /// </summary>
        public string OGRN;

        /// <summary>
        /// БИК
        /// </summary>
        public string BIK;

        /// <summary>
        /// Р/Счет
        /// </summary>
        public string RSchet;

        /// <summary>
        /// К/Счет
        /// </summary>
        public string KSchet;

        #endregion

        #region [из таблицы Ind_predp]

        /// <summary>
        /// Название
        /// </summary>
        public string IPName;

        /// <summary>
        /// ИНН
        /// </summary>
        public string IPINN;

        /// <summary>
        /// ЕГРИП
        /// </summary>
        public string IPEGRIP;

        /// <summary>
        /// Р/Счет
        /// </summary>
        public string IPRSchet;

        /// <summary>
        /// К/Счет
        /// </summary>
        public string IPKSchet;

        #endregion


        /// <summary>
        /// Конструктор по умолчанию
        /// </summary>
        public Contragent()
        {
            id_contragent = 0;
            typeContragent = string.Empty;
            id_fiz_lico = 0;
            id_ur_lico = 0;
            id_ind_predp = 0;
            telHome = string.Empty;
            telWork = string.Empty;
            telMobile = string.Empty;
            email = string.Empty;
            note = string.Empty;
            id_address = 0;

            address = new Address();

            UName = string.Empty;
        }

        /// <summary>
        /// Добавление в БД
        /// </summary>
        public void Insert()
        {
            //сначала адрес добавляем!!!!

            string query2 = string.Empty;

            switch (this.typeContragent)
            {
                case "ФЛ":
                    query2 = string.Format("INSERT INTO dbo.Fiz_lico " +
                        " (Family, Name, FName, Male, Birth, " +
                        " Passport_series, Passport_number, Passport_vidan) " +
                        " VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')",
                        this.Family,
                        this.Name,
                        this.FName,
                        this.Male,
                        string.Format("{0}-{1}-{2}", this.Birth.Year, this.Birth.Month, this.Birth.Day),
                        this.PassportSeries,
                        this.PassportNumber,
                        this.PassportVidan
                        );

                    DB.execute_sql(query2);
                    this.id_fiz_lico = DB.max_num("dbo.Fiz_lico", "id_fiz_lico");

                    break;

                case "ЮЛ":
                    query2 = string.Format("INSERT INTO dbo.Ur_lico " +
                        " (Name, INN, KPP, OGRN, BIK, r_schet, k_schet) " +
                        " VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')",
                        this.UName,
                        this.INN,
                        this.KPP,
                        this.OGRN,
                        this.BIK,
                        this.RSchet,
                        this.KSchet
                        );

                    DB.execute_sql(query2);

                    this.id_ur_lico = DB.max_num("dbo.Ur_lico", "id_ur_lico");

                    break;

                case "ИП":
                    query2 = string.Format("INSERT INTO dbo.Ind_predp " +
                       " (Name, INN, EGRIP, r_schet, k_schet) " +
                       " VALUES ('{0}','{1}','{2}','{3}','{4}')",
                       this.IPName,
                       this.IPINN,
                       this.IPEGRIP,
                       this.IPRSchet,
                       this.IPKSchet
                       );

                    DB.execute_sql(query2);

                    this.id_ind_predp = DB.max_num("dbo.Ind_predp", "id_ind_predp");

                    break;

                default:
                    throw new Exception("Не указан тип контрагента");
                //return;
            }


            string query1 = string.Format("INSERT INTO dbo.Contragent " +
                " (type_contragent, id_fiz_lico, id_ur_lico, id_ind_predp, tel_home, tel_work, " +
                " tel_mob, e_mail, note, id_address) " +
                " VALUES ('{0}',{1},{2},{3},'{4}','{5}','{6}','{7}','{8}',{9})",
                this.typeContragent,
                this.id_fiz_lico != 0 ? "'" + this.id_fiz_lico.ToString() + "'" : "NULL",
                this.id_ur_lico != 0 ? "'" + this.id_ur_lico.ToString() + "'" : "NULL",
                this.id_ind_predp != 0 ? "'" + this.id_ind_predp.ToString() + "'" : "NULL",
                this.telHome,
                this.telWork,
                this.telMobile,
                this.email,
                this.note,
                this.address.id_address != 0 ? "'" + this.address.id_address.ToString() + "'" : "NULL"
                );

            DB.execute_sql(query1);

            this.id_contragent = DB.max_num("dbo.Contragent", "id_contragent");

        }


        /// <summary>
        /// Выборка по идентификатору
        /// </summary>
        /// <param name="id">Идентификатор</param>
        /// <param name="type">Тип контрагента</param>
        /// <returns>Контрагент</returns>
        public Contragent SelectById(int id, string type)
        {
            DataSet ds = new DataSet();

            this.typeContragent = type;

            string viewName = string.Empty;
            switch (type)
            {
                case "ФЛ":
                    viewName = "dbo.View_Contragent_FL";
                    DB.query_table("SELECT * FROM dbo.View_Contragent_FL WHERE id_contragent = " + id, "dbo.View_Contragent_FL", ref ds);

                    if (int.TryParse(ds.Tables[viewName].Rows[0]["id_fiz_lico"].ToString(), out this.id_fiz_lico)) { ;}
                    this.Family = ds.Tables[viewName].Rows[0]["Family"].ToString();
                    this.Name = ds.Tables[viewName].Rows[0]["Name"].ToString();
                    this.FName = ds.Tables[viewName].Rows[0]["FName"].ToString();
                    if (bool.TryParse(ds.Tables[viewName].Rows[0]["Male"].ToString(), out this.Male)) { ;}
                    if (DateTime.TryParse(ds.Tables[viewName].Rows[0]["Birth"].ToString(), out this.Birth)) { ;}
                    this.PassportSeries = ds.Tables[viewName].Rows[0]["Passport_series"].ToString();
                    this.PassportNumber = ds.Tables[viewName].Rows[0]["Passport_number"].ToString();
                    this.PassportVidan = ds.Tables[viewName].Rows[0]["Passport_vidan"].ToString();
                    break;

                case "ЮЛ":
                    viewName = "dbo.View_Contragent_UL";
                    DB.query_table("SELECT * FROM dbo.View_Contragent_UL WHERE id_contragent = " + id, "dbo.View_Contragent_UL", ref ds);

                    if (int.TryParse(ds.Tables[viewName].Rows[0]["id_ur_lico"].ToString(), out this.id_ur_lico)) { ;}
                    this.UName = ds.Tables[viewName].Rows[0]["Name"].ToString();
                    this.INN = ds.Tables[viewName].Rows[0]["INN"].ToString();
                    this.KPP = ds.Tables[viewName].Rows[0]["KPP"].ToString();
                    this.OGRN = ds.Tables[viewName].Rows[0]["OGRN"].ToString();
                    this.BIK = ds.Tables[viewName].Rows[0]["BIK"].ToString();
                    this.RSchet = ds.Tables[viewName].Rows[0]["r_schet"].ToString();
                    this.KSchet = ds.Tables[viewName].Rows[0]["k_schet"].ToString();

                    break;

                case "ИП":
                    viewName = "dbo.View_Contragent_IP";
                    DB.query_table("SELECT * FROM dbo.View_Contragent_IP WHERE id_contragent = " + id, "dbo.View_Contragent_IP", ref ds);

                    if (int.TryParse(ds.Tables[viewName].Rows[0]["id_ind_predp"].ToString(), out this.id_ind_predp)) { ;}
                    this.IPName = ds.Tables[viewName].Rows[0]["Name"].ToString();
                    this.IPINN = ds.Tables[viewName].Rows[0]["INN"].ToString();
                    this.IPEGRIP = ds.Tables[viewName].Rows[0]["EGRIP"].ToString();
                    this.IPRSchet = ds.Tables[viewName].Rows[0]["r_schet"].ToString();
                    this.IPKSchet = ds.Tables[viewName].Rows[0]["k_schet"].ToString();

                    break;
                default:
                    throw new Exception("Не указан тип контрагента при выборе контрагента");
            }

            //DB.query_table("SELECT * FROM dbo.Contragent WHERE id_contragent = " + id, "dbo.Contragent", ref ds);

            if (viewName != string.Empty)
            {
                this.telHome = ds.Tables[viewName].Rows[0]["tel_home"].ToString();
                this.telWork = ds.Tables[viewName].Rows[0]["tel_work"].ToString();
                this.telMobile = ds.Tables[viewName].Rows[0]["tel_mob"].ToString();
                this.email = ds.Tables[viewName].Rows[0]["e_mail"].ToString();
                this.note = ds.Tables[viewName].Rows[0]["note"].ToString();
                if (int.TryParse(ds.Tables[viewName].Rows[0]["id_contragent"].ToString(), out this.id_contragent)) { ;}

                if (int.TryParse(ds.Tables[viewName].Rows[0]["id_address"].ToString(), out this.id_address))
                {
                    if (this.id_address != 0)
                    {
                        this.address = new Address(id_address);
                    }
                }
            }
            return this;
        }

        /// <summary>
        /// Выборка по идентификатору
        /// </summary>
        /// <param name="id">Идентификатор</param>
        /// <param name="type">Тип контрагента</param>
        /// <returns>Контрагент</returns>
        public Contragent SelectById(int id)
        {
            DataSet ds = new DataSet();

            DB.query_table("SELECT id_fiz_lico, id_ur_lico, id_ind_predp FROM dbo.Contragent WHERE id_contragent = " + id, "dbo.Contragent", ref ds);

            string type = "";
            if (int.TryParse(ds.Tables["dbo.Contragent"].Rows[0]["id_fiz_lico"].ToString(), out this.id_fiz_lico)) { type = "ФЛ"; }
            if (int.TryParse(ds.Tables["dbo.Contragent"].Rows[0]["id_ur_lico"].ToString(), out this.id_ur_lico)) { type = "ЮЛ"; }
            if (int.TryParse(ds.Tables["dbo.Contragent"].Rows[0]["id_ind_predp"].ToString(), out this.id_ind_predp)) { type = "ИП"; }

            this.typeContragent = type;

            ds.Clear();
            ds.Tables.Clear();

            string viewName = string.Empty;
            switch (type)
            {
                case "ФЛ":
                    viewName = "dbo.View_Contragent_FL";
                    DB.query_table("SELECT * FROM dbo.View_Contragent_FL WHERE id_contragent = " + id, "dbo.View_Contragent_FL", ref ds);

                    if (int.TryParse(ds.Tables[viewName].Rows[0]["id_fiz_lico"].ToString(), out this.id_fiz_lico)) { ;}
                    this.Family = ds.Tables[viewName].Rows[0]["Family"].ToString();
                    this.Name = ds.Tables[viewName].Rows[0]["Name"].ToString();
                    this.FName = ds.Tables[viewName].Rows[0]["FName"].ToString();
                    if (bool.TryParse(ds.Tables[viewName].Rows[0]["Male"].ToString(), out this.Male)) { ;}
                    if (DateTime.TryParse(ds.Tables[viewName].Rows[0]["Birth"].ToString(), out this.Birth)) { ;}
                    this.PassportSeries = ds.Tables[viewName].Rows[0]["Passport_series"].ToString();
                    this.PassportNumber = ds.Tables[viewName].Rows[0]["Passport_number"].ToString();
                    this.PassportVidan = ds.Tables[viewName].Rows[0]["Passport_vidan"].ToString();
                    break;

                case "ЮЛ":
                    viewName = "dbo.View_Contragent_UL";
                    DB.query_table("SELECT * FROM dbo.View_Contragent_UL WHERE id_contragent = " + id, "dbo.View_Contragent_UL", ref ds);

                    if (int.TryParse(ds.Tables[viewName].Rows[0]["id_ur_lico"].ToString(), out this.id_ur_lico)) { ;}
                    this.UName = ds.Tables[viewName].Rows[0]["Name"].ToString();
                    this.INN = ds.Tables[viewName].Rows[0]["INN"].ToString();
                    this.KPP = ds.Tables[viewName].Rows[0]["KPP"].ToString();
                    this.OGRN = ds.Tables[viewName].Rows[0]["OGRN"].ToString();
                    this.BIK = ds.Tables[viewName].Rows[0]["BIK"].ToString();
                    this.RSchet = ds.Tables[viewName].Rows[0]["r_schet"].ToString();
                    this.KSchet = ds.Tables[viewName].Rows[0]["k_schet"].ToString();

                    break;

                case "ИП":
                    viewName = "dbo.View_Contragent_IP";
                    DB.query_table("SELECT * FROM dbo.View_Contragent_IP WHERE id_contragent = " + id, "dbo.View_Contragent_IP", ref ds);

                    if (int.TryParse(ds.Tables[viewName].Rows[0]["id_ind_predp"].ToString(), out this.id_ind_predp)) { ;}
                    this.IPName = ds.Tables[viewName].Rows[0]["Name"].ToString();
                    this.IPINN = ds.Tables[viewName].Rows[0]["INN"].ToString();
                    this.IPEGRIP = ds.Tables[viewName].Rows[0]["EGRIP"].ToString();
                    this.IPRSchet = ds.Tables[viewName].Rows[0]["r_schet"].ToString();
                    this.IPKSchet = ds.Tables[viewName].Rows[0]["k_schet"].ToString();

                    break;
                default:
                    throw new Exception("Не указан тип контрагента при выборе контрагента");
            }

            //DB.query_table("SELECT * FROM dbo.Contragent WHERE id_contragent = " + id, "dbo.Contragent", ref ds);

            if (viewName != string.Empty)
            {
                this.telHome = ds.Tables[viewName].Rows[0]["tel_home"].ToString();
                this.telWork = ds.Tables[viewName].Rows[0]["tel_work"].ToString();
                this.telMobile = ds.Tables[viewName].Rows[0]["tel_mob"].ToString();
                this.email = ds.Tables[viewName].Rows[0]["e_mail"].ToString();
                this.note = ds.Tables[viewName].Rows[0]["note"].ToString();
                if (int.TryParse(ds.Tables[viewName].Rows[0]["id_contragent"].ToString(), out this.id_contragent)) { ;}

                if (int.TryParse(ds.Tables[viewName].Rows[0]["id_address"].ToString(), out this.id_address))
                {
                    if (this.id_address != 0)
                    {
                        this.address = new Address(id_address);
                    }
                }
            }
            return this;
        }


        /// <summary>
        /// Выборка всех контрагентов
        /// </summary>
        /// <param name="ds"></param>
        public void SelectAll(ref DataSet ds)
        {
            DB.query_table("SELECT * FROM dbo.Contragent WHERE Deleted = 'False'", "dbo.Contragent", ref ds);
        }

        /// <summary>
        /// Выборка по типу
        /// </summary>
        /// <param name="type">Тип контрагента</param>
        /// <param name="ds"></param>
        public void SelectByType(string type, ref DataSet ds)
        {            
            switch (type)
            {
                case "ФЛ": //id_contragent, Фамилия, Имя, Отчество, [Дата рождения]
                    //DB.query_table("SELECT * FROM dbo.View_Contragent_FL_rus", "dbo.View_Contragent_FL_rus", ref ds);
                    DB.query_table("EXEC dbo.GetContragents 'ФЛ', 'False'", "dbo.View_Contragent_FL_rus", ref ds);

                    break;
                case "ЮЛ":
                    //DB.query_table("SELECT * FROM dbo.View_Contragent_UL_rus", "dbo.View_Contragent_UL_rus", ref ds);
                    DB.query_table("EXEC dbo.GetContragents 'ЮЛ', 'False'", "dbo.View_Contragent_UL_rus", ref ds);
                    break;
                case "ИП":
                    //DB.query_table("SELECT * FROM dbo.View_Contragent_IP_rus", "dbo.View_Contragent_IP_rus", ref ds);
                    DB.query_table("EXEC dbo.GetContragents 'ИП', 'False'", "dbo.View_Contragent_IP_rus", ref ds);
                    break;
                default:
                    throw new Exception("Не указан тип контрагента #2");
            }
        }

        public void SelectByArgs()
        {
            ;
        }


        /// <summary>
        /// Обновление записи
        /// </summary>
        public void Update()
        {
            DataSet ds = new DataSet();
            string query1 = string.Format("UPDATE dbo.Contragent SET tel_home = '{0}', tel_work = '{1}', " +
                " tel_mob = '{2}', e_mail = '{3}', note = '{4}', id_address = '{5}'" +
                " WHERE id_contragent = '{6}'",
                this.telHome,
                this.telWork,
                this.telMobile,
                this.email,
                this.note,
                this.address.id_address,
                this.id_contragent
                );

            DB.query_table(query1, "dbo.Contragent", ref ds);

            string query2 = string.Empty;

            switch (this.typeContragent)
            {
                case "ФЛ":
                    query2 = string.Format("UPDATE dbo.Fiz_lico SET Family = '{0}', Name = '{1}', FName = '{2}', Male = '{3}', Birth = '{4}', " +
                        " Passport_series = '{5}', Passport_number = '{6}',  Passport_vidan = '{7}'  WHERE id_fiz_lico = '{8}'",
                        this.Family,
                        this.Name,
                        this.FName,
                        this.Male,
                        string.Format("{0}-{1}-{2}", this.Birth.Year, this.Birth.Month, this.Birth.Day),
                        this.PassportSeries,
                        this.PassportNumber,
                        this.PassportVidan,
                        this.id_fiz_lico);
                    DB.query_table(query2, "dbo.Fiz_lico", ref ds);
                    break;

                case "ЮЛ":
                    query2 = string.Format("UPDATE dbo.Ur_lico SET Name = '{0}', INN = '{1}', KPP = '{2}', OGRN = '{3}', BIK = '{4}', " +
                        " r_schet = '{5}', k_schet = '{6}'  WHERE id_ur_lico = '{7}'",
                        this.UName,
                        this.INN,
                        this.KPP,
                        this.OGRN,
                        this.BIK,
                        this.RSchet,
                        this.KSchet,
                        this.id_ur_lico);
                    DB.query_table(query2, "dbo.Ur_lico", ref ds);
                    break;

                case "ИП":
                    query2 = string.Format("UPDATE dbo.Ind_predp SET Name = '{0}', INN = '{1}', EGRIP = '{2}', " +
                        " r_schet = '{3}', k_schet = '{4}'  WHERE id_ind_predp = '{5}'",
                        this.IPName,
                        this.IPINN,
                        this.IPEGRIP,
                        this.IPRSchet,
                        this.IPKSchet,
                        this.id_ind_predp);
                    DB.query_table(query2, "dbo.Ind_predp", ref ds);
                    break;

                default:
                    throw new Exception("Не указан тип контрагента #2");
            }

        }

        /// <summary>
        /// Удаление контрагента
        /// </summary>
        /// <param name="id">ИД контрагента</param>
        /// <param name="id_consultant_who_delete">ИД консультанта, кто удалил</param>
        /// <param name="reasonDelete">Причина удаления</param>
        /// <param name="showInReport">Отображать в отчетах</param>
        /// <returns>Успешность удаления</returns>
        public bool DeleteById(int id, int id_consultant_who_delete, string reasonDelete, bool showInReport)
        {
            try
            {
                DataSet ds = new DataSet();
                string query1 = string.Format("UPDATE dbo.Contragent SET id_consultant_who_delete = '{0}', ReasonDelete = '{1}', " +
                    " ShowInReport = '{2}', Deleted = 'True', DateDelete = CAST(GETDATE() AS date) " +
                    " WHERE id_contragent = '{3}'",
                    id_consultant_who_delete,
                    reasonDelete,
                    showInReport,
                    id);

                DB.query_table(query1, "dbo.Contragent", ref ds);
            }
            catch(Exception ex)
            {
                string s = ex.ToString();
                return false;
            }
            //удаление прошло успешно
            return true;
        }

    }
}